﻿using System.Data;
using System.Collections.Generic;
using System;
using System.IO;
using System.Text;
using System.Data.OleDb;
using PDOGenerator.MapTypes;


namespace PDOGenerator.Generators
{
    class ToPDOClass : ClassGenerator
    {
        public enum ColumnGenerate
        {
            PrimaryKeysOnly,
            NonPrimaryKeys,
            AllColumns
        }


        private Project _project;
        private StringWriter otw;
        private int _indentTabCount = 3;

        private delegate void PrintLine(ColumnMap col);

        public static GeneratorPM PM
        {
            get { return (PdoApp.Current as PdoApp).PM; }
        }

        internal ToPDOClass(Project p)
        {
            _project = p;

        }

        public override string Generate(TableMap tbl)
        {
            try
            {
                otw = new StringWriter();

                WriteNSClassLeadIn(tbl);
                WriteStaticMembers(tbl);
                WritePrivateMembers(tbl);
                WriteFieldNames(tbl);

                if (tbl.IsLookup)
                {
                    WriteLookupEnum(tbl);
                }

                WriteCtor(tbl);

                otw.WriteLine("\t\t#region Public Properties and Methods");

                WriteInitialize(tbl);

                WriteLINQSupport(tbl);
                WritePublicMembers(tbl);

                if (!tbl.IsView)
                {
                    WriteObjectID(tbl);
                }

                WriteLoadFromDataRow(tbl);

                WriteLoad(tbl);


                if (!tbl.IsView)
                {
                    WriteInsertUpdateDelete(tbl);
                    WriteListAccessors(tbl);
                }

                WriteGetAll(tbl); // static methods to return lists and perform sort

                WriteRequiredFieldValidationRules(tbl); // generate validation rules

                WriteNSClassLeadOut();

                return otw.ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("Error generating class  file for table " + tbl.TableName, ex);
            }
        }


        private void WriteNSClassLeadIn(TableMap tbl)
        {
            Version ver = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version;

            string hdr = string.Format(Templates.FILE_HEADER_BLOCK, ver.Major, ver.Minor,
                _project.ProjectName, tbl.SchemaName, tbl.TableName);

            otw.Write(hdr);

            otw.Write(Templates.BUS_USING_LEAD);
            otw.Write(_project.UsingDirectives);

            string name = _project.NameSpace + (tbl.SchemaName.Equals(_project.DefaultSchemaName) ? "" : "." + tbl.SchemaName);
            otw.WriteLine("\r\nnamespace {0}", name);
            otw.WriteLine("{\r\n");
            otw.WriteLine("\t[Table(Name = \"[{0}].[{1}]\")]", tbl.SchemaName, tbl.TableName); // LINQ
            otw.WriteLine("\t[DataObjectAttribute]", tbl.SchemaName, tbl.TableName); // LINQ
            otw.WriteLine("\t[SProcTemplate(Template = \"[{0}].[{1}{2}{{0}}]\")]", tbl.SchemaName, _project.StoredProcPrefix, tbl.ClassName);

            if (tbl.IsProxyDerived)
            {
                otw.WriteLine("\t[ExtendedPersistableDataObject]");
            }

            if (tbl.IsBaseTable)
            {
                string exColName = tbl.ClassExtenderColumn.ColumnName;
                int pos = exColName.IndexOf(MapTypes.Markers.PROXY_EXTENDER);
                string discrimClassName = exColName.Substring(0, pos);
                string discrimColName = exColName.Substring(pos + MapTypes.Markers.PROXY_EXTENDER.Length);
                // Example: [TypeExtension("ExamType.ExtensionClassName", "ExamGuid:BaseKey")]
                otw.WriteLine("\t[TypeExtension(\"{0}.{1}\", \"{2}:BaseKey\")]",
                    tbl.ClassExtenderColumn.ForeignTable.ClassName, discrimColName, tbl.PrimaryKey().SafeColumnName);
            }

            string classBaseInterfaces = _project.BaseClassName;
            otw.WriteLine("\tpublic partial class {0} : {1}", tbl.ClassName, (tbl.IsProxyDerived ? tbl.BaseClassName : classBaseInterfaces));
            otw.WriteLine("\t{");
        }


        private string DataType(TableMap t, ColumnMap c)
        {
            if (c.IsPK && t.CreateEnum)
            {
                return t.EnumName;
            }
            else
            {
                string s = c.DataType;
                if (c.Nullable)
                {
                    if (DataTypeMap.NullableTypes.Contains(c.DataType))
                    {
                        s += "?";
                    }
                }
                return s;
            }
        }

        private void PrintTableMembers(ColumnGenerate columnSet, TableMap tbl, PrintLine fnPL)
        {

            if (columnSet == ColumnGenerate.AllColumns || columnSet == ColumnGenerate.PrimaryKeysOnly)
            {
                foreach (ColumnMap col in tbl.Columns.Values)
                {
                    if (col.IsPK == true)
                    {
                        fnPL(col);
                    }
                }
            }

            if (columnSet == ColumnGenerate.AllColumns || columnSet == ColumnGenerate.NonPrimaryKeys)
            {
                foreach (ColumnMap col in tbl.Columns.Values)
                {
                    if (col.IsPK == false &&
                        col.Ignore == false &&
                        col.CollectionColumn == null) // CX TEMP - exclude collection columns from this loop - maybe put back here later.
                    {
                        fnPL(col);
                    }
                }
            }


        }


        private void PColPrivateFields(ColumnMap col)
        {
            if (!col.IsCollection && !col.Ignore)
            {
                if (col.IsFK)
                {
                    // Foreign key member property that references
                    // internal object to retrieve fresh ID value when updated.
                    otw.WriteLine("\t\tprivate {0} m_{1};",
                        DataType(col.Table, col),
                        col.SafeColumnName);

                    // Foreign key derived object accessor.
                    if (col.ForeignKeyType == ForeignKeyTypes.KeyAndReadonlyObject)
                    {
                        otw.WriteLine("\r\n\t\t[NonPersisted]");
                    }
                    string foreignSchema = ((col.ForeignTable.SchemaName == col.Table.SchemaName || col.ForeignTable.SchemaName == _project.DefaultSchemaName)
                        ? "" : col.ForeignTable.SchemaName + ".");

                    otw.WriteLine("\t\tprivate {0}{1} m_{2};", foreignSchema, col.ForeignTable.ClassName, col.ObjectPropertyName);
                }
                else
                {
                    // Simple property accessor
                    otw.WriteLine("\t\tprivate {0} m_{1};", DataType(col.Table, col), col.SafeColumnName);
                }
            }
        }

        private void WritePrivateMembers(TableMap tbl)
        {
            otw.WriteLine("\t\t#region Private Members\r\n");

            PrintLine pl = new PrintLine(PColPrivateFields);
            PrintTableMembers(ColumnGenerate.AllColumns, tbl, pl);

            otw.WriteLine("\r\n\t\t#endregion\r\n");
        }


        private void WriteStaticMembers(TableMap tbl)
        {
            otw.WriteLine("\t\t#region Static Members\r\n");

            otw.WriteLine("\t\tpublic static PDORegistration PdoRegistration = PDOMapper.Register(typeof({0}));", tbl.ClassName);

            otw.WriteLine("\r\n\t\t#endregion\r\n");
        }

        private void PColFieldName(ColumnMap col)
        {
            if (!col.Ignore)
            {
                otw.WriteLine("\t\t\tpublic const string {0} = \"{0}\";", col.SafeColumnName);
            }
        }


        private void WriteFieldNames(TableMap tbl)
        {
            PrintLine pl = new PrintLine(PColFieldName);

            otw.WriteLine("\t\t#region Database column Field names");

            string newWord = (tbl.IsProxyDerived ? "new " : "");
            otw.Write(Templates.FIELD_NAME_STRUCT_LEAD, newWord);

            if (tbl.IsProxyDerived)
            {
                TableMap baseTable = _project.AllTables[tbl.BaseClassMoniker];

                otw.WriteLine("\t\t\t// Base Fields from {0}", baseTable.ClassName);
                PrintTableMembers(ColumnGenerate.AllColumns, baseTable, pl);
                otw.WriteLine("\r\n\t\t\t// Locally defined Fields");
            }

            PrintTableMembers(ColumnGenerate.AllColumns, tbl, pl);

            otw.WriteLine("\t\t}");

            otw.WriteLine("\r\n\t\t#endregion\r\n");

        }

        private void PColInitVals(ColumnMap col)
        {
            if (!string.IsNullOrEmpty(col.EntityDefaultValue))
            {
                otw.Write("\r\n\t\t\tm_{0} = {1};", col.SafeColumnName, col.EntityDefaultValue);
            }
            else
            {
                otw.Write("\r\n\t\t\t{0} = {1};", col.SafeColumnName, col.DataType);
            }
        }


        private void WriteLookupEnum(TableMap tbl)
        {
            otw.WriteLine("\t\t#region Database Defined Enumeration support");

            otw.WriteLine(@"
        // Enumeration values as defined in database.
        public enum {0}Key
        {{", tbl.ClassName);

            if (null == tbl.LookupKeyColumn)
            {
                string error = "Lookup Key column not defined for enumeration table " + tbl.TableName;

                if (PM.IsBusy)
                {
                    PM.LastError = "Enumeration Class problem.";
                    PM.LastErrorDetails += "\r\n" + error;
                    return;
                }
                else
                {
                    throw new InvalidOperationException(error);
                }
            }


            OleDbConnection conn = new OleDbConnection(_project.ConnectionString);
            string SQL_GET_LOOKUP_VALUES;
            if (tbl.AlphabetizeEnum)
            {
                SQL_GET_LOOKUP_VALUES = @"select [{0}] from [{1}].[{2}] order by [{0}]";
            }
            else
            {
                SQL_GET_LOOKUP_VALUES = @"select [{0}] from [{1}].[{2}]";
            }

            //E.g. select DeviceSettingTypeGuid from Hydra.DeviceSettingType where ExtensionClassName = 'PPGDeviceSetting'
            string cmd = string.Format(SQL_GET_LOOKUP_VALUES, tbl.LookupKeyColumn.ColumnName, tbl.SchemaName, tbl.TableName);


            OleDbDataAdapter a = new OleDbDataAdapter(cmd, conn);
            DataTable dt = new DataTable();
            a.Fill(dt);


            string line = null;
            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                if (line != null)
                {
                    otw.WriteLine(line);
                }
                string key = (string)dt.Rows[i][0];

                if (key.Contains(" "))
                {
                    string error = string.Format("Enumeration key values cannot contain space characters.  Table '{0}', key value '{1}'", tbl.TableName, key);
                    if (PM.IsBusy)
                    {
                        PM.LastError = "Class deriviation problem.";
                        PM.LastErrorDetails += "\r\n" + error;
                    }
                    else
                    {
                        throw new InvalidOperationException(error);
                    }
                }

                line = string.Format("\t\t\t{0},", key);
            }

            if (line != null)
            {
                // Trim trailing comma off of last line, and write it out.
                line = line.Substring(0, line.Length - 1);
                otw.WriteLine(line);
            }

            conn.Close();

            otw.WriteLine("\t\t};");

            string temp = Templates.LOOKUP_SUPPORT_1;
            temp = temp.Replace("{0}", tbl.LookupKeyColumn.SafeColumnName);
            temp = temp.Replace("{1}", tbl.ClassName);
            otw.Write(temp);

            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                //string key = CFormat.StringSafe(rdr.GetValue(rdr.GetOrdinal(tbl.LookupKeyColumn.ColumnName)));
                string key = (string)dt.Rows[i][0];

                line = string.Format("\t\t\tpublic static {0} {1} {{ get {{ return GetEnumerationItem({0}Key.{1}); }} }}", tbl.ClassName, key);
                otw.WriteLine(line);
            }

            otw.WriteLine("\t\t}\r\n\r\n\t\t#endregion\r\n");
        }


        private void WriteCtor(TableMap tbl)
        {
            otw.WriteLine("\t\t#region Constructors");
            otw.Write(Templates.BUS_CTOR_LEAD, tbl.ClassName);

            otw.Write(Templates.BUS_CTOR2);

            if (tbl.IsProxyDerived)
            {
                otw.WriteLine(Templates.DERIVED_CONSTRUCTOR_LEAD, tbl.ClassName, tbl.BaseClassName);
            }
            else
            {
                otw.WriteLine();
            }

            otw.WriteLine("\t\t#endregion\r\n");
        }

        private void WriteInitialize(TableMap tbl)
        {
            PrintLine pl = new PrintLine(PColInitVals);

            otw.Write(Templates.OBJECT_INITIALIZE);

            if (tbl.IsProxyDerived)
            {
                otw.WriteLine("\r\n\t\t\t{0} = new Guid(\"{1}\"); // Set ExtendedType key", tbl.ExtensionTablePKName, tbl.ExtensionTypeGuid);
            }

            PrintTableMembers(ColumnGenerate.AllColumns, tbl, pl); // initialization from database defaults

            // entities
            otw.WriteLine("\r\n");
            foreach (ColumnMap col in tbl.Columns.Values)
            {
                if (col.IsFK)
                {
                    otw.WriteLine("\t\t\tm_{0} = null;", col.ObjectPropertyName);
                }
            }

            // entities list
            List<string> entitiesList = GetListAccessorNames(tbl);
            if (entitiesList.Count > 0)
            {
                otw.WriteLine("");
                foreach (string listName in entitiesList)
                {
                    otw.WriteLine("\t\t\tm_{0}List = null;", listName);
                }
            }

            otw.Write(Templates.OBJECT_INITIALIZE_TAIL);
        }

        private void WriteLINQSupport(TableMap tbl)
        {
            string newWord = (tbl.IsProxyDerived ? "new " : "");

            string temp = Templates.LINQ_SUPPORT.Replace("{0}", tbl.ClassName);
            temp = temp.Replace("{1}", newWord);
            otw.Write(temp);
        }

        static private void SetColumnDefault(ColumnMap col, string nonNullDefaultValue)
        {
            SetColumnDefault(col, nonNullDefaultValue, false);
        }

        /// <summary>
        /// Set column default value.
        /// </summary>
        /// <param name="col"></param>
        /// <param name="nonNullDefaultValue"></param>
        /// <param name="addQuotes"></param>
        static private void SetColumnDefault(ColumnMap col, string nonNullDefaultValue, bool addQuotes)
        {
            string defaultString = null;

            if (addQuotes)
            {
                if (!string.IsNullOrEmpty(col.DatabaseDefaultValue))
                {
                    defaultString = "\"" + col.DatabaseDefaultValue + "\"";
                }
            }
            else
            {
                defaultString = col.DatabaseDefaultValue;
            }

            if (string.IsNullOrEmpty(defaultString))
            {
                if (col.Nullable)
                {
                    defaultString = "null";
                }
                else
                {
                    if (addQuotes)
                    {
                        if (!string.IsNullOrEmpty(nonNullDefaultValue))
                        {
                            defaultString = "\"" + nonNullDefaultValue + "\"";
                        }
                    }
                    else
                    {
                        defaultString = nonNullDefaultValue;
                    }
                }
            }

            if (col.DataType == "decimal")
            {
                if (!(defaultString == "null" || defaultString.EndsWith("M")))
                {
                    defaultString += "M";
                }
            }
            col.EntityDefaultValue = string.IsNullOrEmpty(defaultString) ? "null" : defaultString;
        }

        /// <summary>
        /// Set column default for non-nullable, numeric fields.
        /// </summary>
        /// <param name="col"></param>
        /// <param name="nonNullDefaultValue"></param>
        static private void SetColumnDefaultNN(ColumnMap col, string nonNullDefaultValue)
        {
            string defaultString = col.DatabaseDefaultValue;

            if (string.IsNullOrEmpty(defaultString))
            {
                defaultString = nonNullDefaultValue;
            }

            col.EntityDefaultValue = string.IsNullOrEmpty(defaultString) ? "null" : defaultString;
        }



        public static void SetColumnInferredProperties(ColumnMap col)
        {
            switch (col.DBType)
            {
                case "int":
                    if (col.Nullable)
                    {
                        col.DbConversionFunctionName = "IntNullable";
                    }
                    else
                    {
                        col.DbConversionFunctionName = "IntSafe";
                    }
                    col.SetFunctionName = "SetInt";

                    SetColumnDefault(col, "0");

                    break;

                case "bigint":
                    if (col.Nullable)
                    {
                        col.DbConversionFunctionName = "Int64Nullable";
                    }
                    else
                    {
                        col.DbConversionFunctionName = "Int64Safe";
                    }
                    col.SetFunctionName = "SetInt64";

                    SetColumnDefault(col, "0");
                    break;

                case "smallint":
                    if (col.Nullable)
                    {
                        col.DbConversionFunctionName = "Int16Nullable";
                    }
                    else
                    {
                        col.DbConversionFunctionName = "Int16Safe";
                    }
                    col.SetFunctionName = "SetInt16";

                    SetColumnDefault(col, "0");
                    break;

                case "tinyint":
                    if (col.Nullable)
                    {
                        col.DbConversionFunctionName = "ByteNullable";
                    }
                    else
                    {
                        col.DbConversionFunctionName = "ByteSafe";
                    }
                    col.SetFunctionName = "SetByte";

                    SetColumnDefault(col, "0");
                    break;

                case "bit":
                    col.DbConversionFunctionName = "BoolSafe";
                    col.SetFunctionName = "SetBool";

                    if (col.DatabaseDefaultValue == "1")
                    {
                        col.EntityDefaultValue = "true";
                    }
                    else
                    {
                        col.EntityDefaultValue = "false";
                    }
                    break;

                case "sysname":
                    col.DbConversionFunctionName = "StringSafe";
                    col.SetFunctionName = "SetString";
                    col.MaxStringLength = col.ColumnPrecision;

                    SetColumnDefault(col, null, true);
                    break;

                case "char":
                case "varchar":
                case "nchar":
                case "nvarchar":
                    col.DbConversionFunctionName = "StringSafe";
                    col.SetFunctionName = "SetString";
                    col.MaxStringLength = col.ColumnPrecision;

                    SetColumnDefault(col, null, true);

                    break;

                case "varbinary":
                    col.DbConversionFunctionName = "ImageSafe";
                    col.SetFunctionName = "SetByteArray";
                    col.MaxStringLength = col.ColumnPrecision;
                    SetColumnDefault(col, null);
                    break;

                case "ntext": //unicode characters, max length = 1,073,741,823
                    col.DbConversionFunctionName = "StringSafe";
                    col.SetFunctionName = "SetString";
                    col.MaxStringLength = 1073741823;
                    SetColumnDefault(col, null, true);
                    break;

                case "text": // ascii characters,  max length = 2,147,483,647
                    col.DbConversionFunctionName = "StringSafe";
                    col.SetFunctionName = "SetString";
                    col.MaxStringLength = 2147483647;
                    SetColumnDefault(col, null, true);
                    break;

                case "sql_variant":
                    col.DbConversionFunctionName = "VariantSafe";
                    col.SetFunctionName = "SetObject";
                    SetColumnDefault(col, null);
                    break;

                case "date":
                case "datetime":
                case "smalldatetime":
                    col.ColumnScale = 0;
                    if (col.Nullable)
                    {
                        col.DbConversionFunctionName = "DateTimeNullable";
                    }
                    else
                    {
                        col.DbConversionFunctionName = "DateTimeSafe";
                    }

                    if (!string.IsNullOrEmpty(col.DatabaseDefaultValue))
                    {
                        if (col.DatabaseDefaultValue.ToLower().Contains("getdate"))
                        {
                            col.EntityDefaultValue = "DateTime.Now";
                        }
                        else
                        {
                            string dvalue = col.DatabaseDefaultValue.Replace("'", "").Replace("(", "").Replace(")", "");
                            col.EntityDefaultValue = "DateTime.Parse(\"" + dvalue + "\")";
                        }
                    }
                    else
                    {
                        if (col.Nullable)
                        {
                            col.EntityDefaultValue = "null";
                        }
                        else
                        {
                            col.EntityDefaultValue = "DateTime.Now";
                        }
                    }

                    col.SetFunctionName = "SetDateTime";
                    break;


                case "real":    // a SQl real is -3.40e+38 to +3.40e+38, a C# float is ±1.5e-45 to ±3.4e+38
                    col.DbConversionFunctionName = "FloatSafe";
                    col.SetFunctionName = "SetFloat";
                    SetColumnDefaultNN(col, "0");
                    break;

                case "float": // a SQL float is -1.79e+308 to +1.79e+308, a C# double is ±5.0e-324 to ±1.7e+308
                    col.DbConversionFunctionName = "DoubleSafe";
                    col.SetFunctionName = "SetDouble";
                    SetColumnDefaultNN(col, "0");
                    break;

                case "money":
                case "smallmoney":
                    col.ColumnScale = 0; // suppress specification of scale and precision for money and smallmoney types
                    if (col.Nullable)
                    {
                        col.DbConversionFunctionName = "DecimalNullable";
                    }
                    else
                    {
                        col.DbConversionFunctionName = "DecimalSafe";
                    }
                    col.SetFunctionName = "SetDecimal";
                    SetColumnDefault(col, "0.0M");
                    break;

                case "numeric": // a SQL numeric is -10^38 +1 to 10^38 –1
                case "decimal":
                    if (col.Nullable)
                    {
                        col.DbConversionFunctionName = "DecimalNullable";
                    }
                    else
                    {
                        col.DbConversionFunctionName = "DecimalSafe";
                    }
                    col.SetFunctionName = "SetDecimal";
                    SetColumnDefault(col, "0.0M");
                    break;

                case "uniqueidentifier":
                    if (col.Nullable)
                    {
                        col.DbConversionFunctionName = "GuidNullable";
                    }
                    else
                    {
                        col.DbConversionFunctionName = "GuidSafe";
                    }
                    col.SetFunctionName = "SetGuid";

                    if (!string.IsNullOrEmpty(col.DatabaseDefaultValue))
                    {
                        if (col.DatabaseDefaultValue == "newid()")
                        {
                            col.EntityDefaultValue = "Guid.Empty"; // replace with something generated by DataSession??
                        }
                        else
                        {
                            string gvalue = col.DatabaseDefaultValue.Replace("'", "").Replace("{", "").Replace("}", "");
                            col.EntityDefaultValue = "new Guid(\"" + gvalue + "\")";
                        }
                    }
                    else
                    {
                        if (col.Nullable)
                        {
                            col.EntityDefaultValue = "null";
                        }
                        else
                        {
                            col.EntityDefaultValue = "Guid.Empty";
                        }
                    }
                    break;

                case "image":
                    col.DbConversionFunctionName = "ImageSafe";
                    col.SetFunctionName = "SetByteArray";
                    SetColumnDefault(col, "null");
                    break;

                case "xml":
                    col.DbConversionFunctionName = "StringSafe";
                    col.SetFunctionName = "SetString";
                    col.MaxStringLength = 2147483647;
                    SetColumnDefault(col, "null");
                    break;

                default:
                    col.DbConversionFunctionName = "ERRSAFE";
                    col.EntityDefaultValue = col.DatabaseDefaultValue;
                    col.SetFunctionName = "SetObject";
                    break;
            }
        }


        private string BoolString(bool value)
        {
            return (value ? "true" : "false");
        }


        private void PColVar(ColumnMap col)
        {
            string attributeNameParam = string.Empty;

            if (col.SafeColumnName != col.ColumnName)
            {
                attributeNameParam = ", Name = \"" + col.ColumnName + "\"";
            }

            string csharpType = col.DataType;
            if (col.IsFK)
            {
                TableMap otherTable = _project.AllTables[col.CollectionTableMoniker];
                ColumnMap otherColumn = otherTable.PrimaryKey();

                // Foreign key member property that references
                // internal object to retrieve fresh ID value when updated.
                otw.WriteLine(Templates.CORE_OBJECT_ID_ACCESSOR,
                    DataType(col.Table, col),
                    col.SafeColumnName,
                    "",
                    col.ObjectPropertyName,
                    otherColumn.ColumnName,
                    col.SetFunctionName,
                    attributeNameParam);


                // Foreign key derived object accessor.
                string foreignSchema = ((col.ForeignTable.SchemaName == col.Table.SchemaName || col.ForeignTable.SchemaName == _project.DefaultSchemaName)
                        ? "" : col.ForeignTable.SchemaName + ".");

                string foreignClassName = foreignSchema + col.ForeignTable.ClassName;


                string fmt;
                if (csharpType == "string")
                {
                    fmt = string.Format(Templates.STRING_FKEY_OBJECT_LOAD,
                        foreignClassName,
                        col.ObjectPropertyName,
                        col.SafeColumnName, otherColumn.ColumnName, csharpType, col.ReferencedListName);
                }
                else if (csharpType == "Guid")
                {
                    fmt = string.Format(Templates.GUID_FKEY_OBJECT_LOAD,
                        foreignClassName,
                        col.ObjectPropertyName,
                        col.SafeColumnName, otherColumn.ColumnName, csharpType, col.ReferencedListName,
                        col.EntityDefaultValue);
                }
                else
                {
                    fmt = string.Format(Templates.CORE_FKEY_OBJECT_LOAD,
                        foreignClassName,
                        col.ObjectPropertyName,
                        col.SafeColumnName, otherColumn.ColumnName, csharpType, col.ReferencedListName);
                }
                otw.Write(fmt + "\r\n");
            }
            else
            {
                // Simple property accessor
                otw.WriteLine("\r\n\t\t/// <summary>");
                otw.WriteLine("\t\t/// Accessor for {0}", col.ColumnName);
                otw.WriteLine("\t\t/// </summary>");
                otw.WriteLine("\t\t[Column(Storage = \"m_{0}\"{1}{2})]", col.SafeColumnName, attributeNameParam, (col.IsPK ? ", IsPrimaryKey = true" : ""));
                otw.WriteLine("\t\t[DataObjectFieldAttribute({0}, {1}, {2})]", BoolString(col.IsPK), BoolString(col.Identity), BoolString(col.Nullable));
                otw.WriteLine("\t\tpublic {0} {1}", DataType(col.Table, col), col.SafeColumnName);
                otw.WriteLine("\t\t{");
                otw.WriteLine("\t\t\tget {{ return m_{0}; }}", col.SafeColumnName);

                if (csharpType.Equals("String", StringComparison.OrdinalIgnoreCase))
                {
                    // Use the SetString overload that checks for max string length.
                    otw.WriteLine(string.Format("\t\t\tset {{ {0}(ref m_{1}, value, {2}, () => this.{1}); }}",
                        col.SetFunctionName, col.SafeColumnName, col.MaxStringLength));
                }
                else
                {
                    otw.WriteLine(string.Format("\t\t\tset {{ {0}(ref m_{1}, value, () => this.{1}); }}",
                        col.SetFunctionName, col.SafeColumnName));
                }
                otw.WriteLine("\t\t}\r\n");
            }

            if (csharpType.Equals("String", StringComparison.OrdinalIgnoreCase))
            {
                // Emit string max length property
                otw.WriteLine("\t\t/// <summary>");
                otw.WriteLine("\t\t/// Maximum allowed length of {0}", col.SafeColumnName);
                otw.WriteLine("\t\t/// </summary>");
                otw.WriteLine("\t\t[XmlIgnore]");
                otw.WriteLine("\t\tpublic static int {0}MaxLength", col.SafeColumnName);
                otw.WriteLine("\t\t{{ get {{ return {0}; }} }}", col.MaxStringLength);
            }
        }



        private void WritePublicMembers(TableMap tbl)
        {
            PrintLine pl = new PrintLine(PColVar);

            PrintTableMembers(ColumnGenerate.AllColumns, tbl, pl);
        }


        private void WriteObjectID(TableMap tbl)
        {
            ColumnMap pkey = tbl.PrimaryKey();
            if (pkey == null)
            {
                throw new InvalidOperationException("No primary key defined for table " + tbl.TableName);
            }

            if (pkey.DataType == "Guid")
            {
                otw.Write(Templates.GUID_ID, pkey.ColumnName);
            }
            else
            {
                otw.Write(Templates.CORE_ID, pkey.ColumnName, pkey.DataType);
            }


            // temporary test  only - uncomment before checkin 
            //if (!tbl.IsProxyDerived)
            //{
            //    // Separate property to access primary key generated only for non-derived classes.
            //    otw.Write(Templates.BASE_PROXY_CORE_ID, pkey.ColumnName);
            //}

        }

        private string indentTabString()
        {
            return IndentTabString(_indentTabCount);
        }

        public static string IndentTabString(int indentTabCount)
        {
            string indentString = string.Empty;
            for (int i = 0; i < indentTabCount; i++)
            {
                indentString += "\t";
            }
            return indentString;
        }

        private void PColGetValFromReader(ColumnMap col)
        {
            otw.WriteLine("{0}m_{1} = SqlConvert.{2}(dr[\"{3}\"]);",
                indentTabString(), col.SafeColumnName, col.DbConversionFunctionName, col.SafeColumnName);
        }

        private void WriteLoadFromDataRow(TableMap tbl)
        {
            PrintLine pl = new PrintLine(PColGetValFromReader);

            otw.WriteLine(Templates.LOAD_FROM_DATA_ROW_HEAD);

            if (tbl.IsProxyDerived)
            {
                _indentTabCount += 1;
                string name = null;
                try
                {
                    name = tbl.BasePKeyColumn.SafeColumnName;

                    TableMap baseTable = _project.AllTables[tbl.BaseClassMoniker];

                    // CX NOTE: Want to call LoadFromDataRow when data contains base class(es) data.
                    // Othewise, need to do a separate database hit by calling Load() with pkey value.
                }
                catch (Exception ex)
                {
                    throw new Exception("BaseKey not properly defined for table " + tbl.TableName, ex);
                }
                otw.WriteLine(Templates.LOAD_FROM_DATA_ROW_SECTION_HEAD_DERIVED, name);
            }
            else
            {
                otw.WriteLine(string.Empty); // just a visual break
            }

            _indentTabCount += 1;
            PrintTableMembers(ColumnGenerate.AllColumns, tbl, pl);
            _indentTabCount -= 1;

            if (tbl.IsProxyDerived)
            {
                otw.WriteLine(Templates.LOAD_FROM_DATA_ROW_SECTION_TAIL_DERIVED, tbl.BasePKeyColumn.SafeColumnName);
                _indentTabCount -= 1;
            }
            else
            {
                otw.WriteLine(Templates.LOAD_FROM_DATA_ROW_TAIL);
            }
        }


        private void WriteLoad(TableMap tbl)
        {
            System.Text.StringBuilder sParamList = new System.Text.StringBuilder(15);
            System.Text.StringBuilder sFieldList = new System.Text.StringBuilder(15);


            if (tbl.IsView)
            {
                otw.Write(Templates.CLASS_LOAD_VIEW, tbl.TableName, tbl.SchemaName);
            }
            else
            {
                ColumnMap col;

                col = tbl.PrimaryKey();
                if (null == col)
                {
                    throw new Exception("Missing primary key on table " + tbl.TableName);
                }

                sParamList.Append(col.DataType + " " + col.ColumnName);
                sFieldList.Append(col.ColumnName + "");
                string temp = Templates.CORE_LOAD.Replace("{0}", "SqlConvert." + col.DbConversionFunctionName + "(pkeyID)");

                temp = Templates.CLASS_LOAD_TABLE.Replace("{0}", sParamList.ToString());
                temp = temp.Replace("{1}", sFieldList.ToString());

                string newWord = (tbl.IsProxyDerived ? "new " : "");
                temp = temp.Replace("{2}", newWord);
                temp = temp.Replace("{3}", tbl.ClassName);
                otw.Write(temp);
            }
        }


        private void WriteInsertUpdateDelete(TableMap tbl)
        {
            ColumnMap pkeyColumn = tbl.PrimaryKey();

            // insert
            string temp;
            if (pkeyColumn.DataType == "string")
            {
                if (tbl.IsProxyDerived)
                {
                    temp = Templates.STRING_PKEY_DERIVED_INSERT;
                }
                else
                {
                    temp = Templates.STRING_PKEY_INSERT;
                }
            }
            else if (pkeyColumn.DataType == "Guid")
            {
                if (tbl.IsProxyDerived)
                {
                    temp = Templates.GUID_PKEY_DERIVED_INSERT;
                }
                else
                {
                    temp = Templates.GUID_PKEY_INSERT;
                }
            }
            else // primary key is a numeric value type
            {
                if (tbl.IsProxyDerived)
                {
                    temp = Templates.CORE_DERIVED_INSERT;
                }
                else
                {
                    temp = Templates.CORE_INSERT;
                }
            }

            string columnParamList = GetSelectColumnList(tbl, !pkeyColumn.Identity, false, false, true);
            temp = temp.Replace("{0}", columnParamList);
            temp = temp.Replace("{1}", pkeyColumn.SafeColumnName);
            temp = temp.Replace("{2}", pkeyColumn.DataType);
            temp = temp.Replace("{3}", tbl.ClassName);

            if (tbl.IsProxyDerived)
            {
                TableMap baseTable = _project.AllTables[tbl.BaseClassMoniker];
                temp = temp.Replace("{4}", tbl.BasePKeyColumn.SafeColumnName);
                temp = temp.Replace("{5}", baseTable.PrimaryKey().ColumnName);
            }

            otw.Write(temp);


            columnParamList = GetSelectColumnList(tbl, false, false, false, true);

            temp = Templates.CORE_DERIVED_UPDATE;
            temp = temp.Replace("{0}", pkeyColumn.ColumnName + ", " + columnParamList);
            temp = temp.Replace("{1}", tbl.TableName);
            temp = temp.Replace("{3}", tbl.ClassName);
            otw.Write(temp);


            temp = Templates.CORE_DERIVED_DELETE;
            temp = temp.Replace("{0}", pkeyColumn.ColumnName);
            temp = temp.Replace("{3}", tbl.ClassName);
            otw.Write(temp);

        }


        public static string GetSelectColumnList(TableMap tbl, bool includePrimaryKeyColumn, bool includeCalculatedColumns,
            bool sqlFormat, bool wrap)
        {
            return GetSelectColumnList(tbl, includePrimaryKeyColumn, includeCalculatedColumns, sqlFormat, wrap, (sqlFormat ? 3 : 7));
        }

        public static string GetSelectColumnList(TableMap tbl, bool includePrimaryKeyColumn, bool includeCalculatedColumns,
            bool sqlFormat, bool wrap, int indentTabCount)
        {
            StringBuilder sbColList = new StringBuilder();
            string indent = "\r\n" + IndentTabString(indentTabCount);

            string columnName;
            if (includePrimaryKeyColumn)
            {
                if (sqlFormat)
                {
                    columnName = tbl.PrimaryKey().ColumnSelectName;
                }
                else
                {
                    columnName = tbl.PrimaryKey().SafeColumnName;
                }
                sbColList.Append(columnName + ", ");
            }


            int lineLength = 0;

            foreach (ColumnMap col in tbl.Columns.Values)
            {
                if (!col.IsPK && !col.IsCollection && !col.Ignore && (!col.Calculated || includeCalculatedColumns))
                {
                    if (sqlFormat)
                    {
                        columnName = col.ColumnSelectName;
                    }
                    else
                    {
                        columnName = col.SafeColumnName;
                    }

                    if (lineLength > 40 && wrap)
                    {
                        sbColList.Remove(sbColList.Length - 1, 1); // remove trailing space
                        sbColList.Append(indent);
                        lineLength = 0;
                    }
                    lineLength += columnName.Length;

                    sbColList.Append(columnName);

                    sbColList.Append(", ");
                }
            }

            return sbColList.ToString(0, sbColList.Length - 2);
        }





        /// <summary>
        /// Write access code for PDO lists.
        /// </summary>
        /// <param name="tbl"></param>
        private List<string> GetListAccessorNames(TableMap tbl)
        {
            List<string> listAccessorNames = new List<string>();

            // TODO: Refactor this to provide the listAccessorName as a property on ColumnMap.
            // Some of this logic is duplicated in WriteListAccessors() - consolidate.

            foreach (ColumnMap col in tbl.Columns.Values)
            {
                if (col.CollectionColumn != null)
                {
                    TableMap table = _project.AllTables[col.CollectionTableMoniker];
                    ColumnMap column = table.Columns[col.CollectionColumn];

                    if (!col.IsSingularRelationship)
                    {
                        bool isExternalSchema = !table.SchemaName.Equals(tbl.SchemaName) && table.SchemaName != _project.DefaultSchemaName;

                        // Check for self referencing columns, change base object name to reduce ambiguity
                        string listAccessorName = (isExternalSchema ? table.SchemaName : "") + col.ReferencedListName;

                        listAccessorNames.Add(listAccessorName);
                    }
                }
            }

            return listAccessorNames;
        }


        /// <summary>
        /// Write access code for PDO lists.
        /// </summary>
        /// <param name="tbl"></param>
        private void WriteListAccessors(TableMap tbl)
        {
            foreach (ColumnMap col in tbl.Columns.Values)
            {
                if (col.CollectionColumn != null)
                {
                    TableMap table = _project.AllTables[col.CollectionTableMoniker];
                    ColumnMap column = table.Columns[col.CollectionColumn];

                    string safeTableName = col.ForeignTable.ClassName;
                    string fmt = null;
                    if ((column.DataType == "Guid") ||
                        (column.DataType == "string"))
                    {
                        if (col.IsSingularRelationship)
                        {
                            // This foreign key relationship is to a table with unique constraint on the referenced column,
                            // so it's actually a one-to-one relationship, not a one-to-many.
                            // In that case, we generate a single object accessor instead of a list accessor.
                            fmt = Templates.STRING_GUID_REFERENCED_TABLE_OBJECT_SINGLETON;
                        }
                        else
                        {
                            fmt = Templates.STRING_GUID_REFERENCED_TABLE_OBJECT_LIST;
                        }
                    }
                    else
                    {
                        fmt = Templates.REFERENCED_TABLE_OBJECT_LIST;
                    }

                    bool isExternalSchema = !table.SchemaName.Equals(tbl.SchemaName) && table.SchemaName != _project.DefaultSchemaName;

                    // Check for self referencing columns, change base object name to reduce ambiguity
                    string listAccessorName = (isExternalSchema ? table.SchemaName : "") + col.ReferencedListName;

                    string colSelect = GetSelectColumnList(table, true, true, true, false);
                    //string otherKey = tbl.PrimaryKey().ObjectPropertyName;
                    string s = string.Format(fmt,
                            safeTableName,
                            listAccessorName,
                            column.ColumnName, //2
                            table.TableName,
                            column.DataType,
                            table.SchemaName,
                            (isExternalSchema ? table.SchemaName + "." : ""),
                            tbl.PrimaryKey().ColumnName, //7
                            colSelect,
                            column.SafeColumnName);

                    otw.Write(s);
                }
            }

        }


        private void WriteGetAll(TableMap tbl)
        {
            string newWord = (tbl.IsProxyDerived ? "new " : "");
            if (!tbl.IsView)
            {
                otw.WriteLine(Templates.CORE_COMPLETE_LIST_LOAD_FROM_TABLE, tbl.ClassName, newWord);
            }
            else
            {
                otw.WriteLine(Templates.CORE_COMPLETE_LIST_LOAD_FROM_VIEW, tbl.ClassName, tbl.SchemaName);
            }
        }


        private void WriteRequiredFieldValidationRules(TableMap tbl)
        {
            otw.WriteLine(Templates.VALIDATION_RULES_BEGIN);

            foreach (ColumnMap col in tbl.Columns.Values)
            {
                if (col.Ignore)
                    continue;

                if (col.IsString)
                {
                    if (!col.Nullable)
                    {
                        otw.WriteLine(Templates.REQUIRED_STRING_VALIDATION_RULE, col.SafeColumnName);
                        otw.WriteLine(Templates.REQUIRED_FIELD_STATUS_RULE, col.SafeColumnName);
                    }

                    otw.WriteLine(Templates.MAX_LENGTH_STRING_VALIDATION_RULE, col.SafeColumnName);
                }

                //============================================================================================
                // DEPRECATED
                // NOTE: There are too many possible dependencies and fkey updates that happen during 
                // the course of saving to make this type of fkey check practicable.

                //if (col.IsFK && col.Nullable == false)
                //{
                //    // Foreign key derived object accessor.
                //    string foreignSchema = ((col.ForeignTable.SchemaName == col.Table.SchemaName || col.ForeignTable.SchemaName == _project.DefaultSchemaName)
                //            ? "" : col.ForeignTable.SchemaName + ".");

                //    string foreignClassName = foreignSchema + col.ForeignTable.ClassName;

                //    otw.WriteLine(Templates.REQUIRED_FKEY_VALIDATION_RULE, col.ObjectPropertyName, col.SafeColumnName, foreignClassName);
                //}
                //============================================================================================
            }

            otw.WriteLine("\t\t#endregion");
        }


        private void WriteNSClassLeadOut()
        {
            otw.WriteLine("\r\n\t\t#endregion\r\n");

            otw.WriteLine("\t}\r\n}");
        }


    }
}
